﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agro.Models;
using System.Data;
using SN.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Agro.Services
{
    public class ReportSearch : BaseService, IReportSearch
    {
        public T_Report GetReport(string reportid)
        {
            T_Report model = DB.From<T_Report>().Where(T_Report._.Report_ID == reportid).ToSingle();
            
            return model;

        }

        //此方法暂不用
        public string ReportCompute(string reportid, string year, string month1, string month2, string zb, string wd, string dwid)
        {
            DataTable ZbTable = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == reportid).ToDataTable();
            T_Organization org = DB.Single<T_Organization>(T_Organization._.Organization_ID == dwid);
            T_Report report = GetEntity<T_Report>(T_Report._.Report_ID == reportid);
            WhereClip wclip = T_ZBSJ._.ZBSJ_ReportID == reportid;


            StringBuilder Builder = new StringBuilder();

            string par = "";
            switch (report.Report_Cycle)
            {
                case "01":
                    par = "月";
                    Builder.AppendFormat("<div style='padding:10px'>查询年度:{0}&nbsp;&nbsp;&nbsp;期间:{1}{3}至{2}{3}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div>", year, month1, month2, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "02":
                    par = "季度";
                    if (int.Parse(month1) > 12)
                    {
                        month1 = (int.Parse(month1) - 12).ToString();
                        month2 = (int.Parse(month2) - 12).ToString();
                    }
                    Builder.AppendFormat("<div style='padding:10px'>查询年度:{0}&nbsp;&nbsp;&nbsp;期间:{1}{3}至{2}{3}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div>", year, month1, month2, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "03":
                    Builder.AppendFormat("<div style='padding:10px'>查询年度:{0}&nbsp;&nbsp;&nbsp;</div>", year);
                    break;
            }
            Builder.Append("<div style='padding:10px;'>");
            Builder.Append("<table id='TableMain'>");
            Builder.Append("<tr><td class='tdcenter'></td>");


            if (!string.IsNullOrEmpty(dwid))
            {
                wclip = wclip && T_ZBSJ._.ZBSJ_DWID.In(dwid);
                wclip = wclip && T_ZBSJ._.ZBSJ_YEAR == year;
            }

            DataTable ZBSJTable = DB.From<T_ZBSJ>().Where(wclip).ToDataTable();




            zb = zb.Remove(zb.Length - 1);
            wd = wd.Remove(wd.Length - 1);

            string[] zbarr = zb.Split(',');
            string[] wdarr = wd.Split(',');
            int zbcount = zbarr.Length;
            int wdcount = wdarr.Length;
            // DataTable dtResult = ZbTable.Clone();



            for (int i = 0; i < zbcount; i++)
            {
                Builder.AppendFormat("<td class='tdcenter' rowspan={1}>{0}</td>", zbarr[i], wdcount);
            }
            Builder.Append("</tr>");
            Builder.Append("<tr>");
            for (int i = 0; i < zbcount; i++)
            {
                for (int j = 0; j < wdcount; j++)
                {
                    Builder.AppendFormat("<td class='tdcenter'>{0}</td>", wdarr[i]);
                }
            }


            Builder.Append("</tr>");


            for (int j = 0; j < wdcount; j++)
            {

                Builder.AppendFormat("<tr><td>{0}</td>", wdarr[j]);
                for (int i = 0; i < zbcount; i++)
                {
                    DataRow[] rows = ZbTable.Select("zbzd_index='" + wdarr[j] + "' and zbzd_dimension='" + zbarr[i] + "'");
                    if (rows.Length > 0)
                    {
                        DataRow[] zbrows = ZBSJTable.Select("ZBSJ_ZBBH='" + rows[0]["ZBZD_ZBBH"] + "'");

                        if (zbrows.Length > 0)
                        {
                            double d = 0;
                            double value;
                            string m = string.Empty;
                            foreach (DataRow row in zbrows)
                            {

                                if (double.TryParse(row["ZBSJ_VALUE"].ToString(), out value))//判断是否数字还是字符串，否则出错。
                                    d += value;
                                else
                                {
                                    m = m + row["ZBSJ_VALUE"].ToString() + "";
                                }
                            }

                            Builder.AppendFormat("<td>{0}{1}</td>", d, m);
                        }
                        else
                            Builder.AppendFormat("<td>{0}</td>", "");
                    }
                    else
                        Builder.AppendFormat("<td>{0}</td>", "");

                }

                Builder.Append("</tr>");
            }

            Builder.Append("</table>");
            Builder.Append("</div>");
            return Builder.ToString();

        }
        public string ReportCompute(string reportid, string year, string month1, string month2, string zb, string wd, string[] dwid)
        {
            zb = zb.Remove(zb.Length - 1);
            wd = wd.Remove(wd.Length - 1);



            string[] zbarr = zb.Split(',');
            string[] wdarr = wd.Split(',');
            int zbcount = zbarr.Length;
            int wdcount = wdarr.Length;
            // DataTable dtResult = ZbTable.Clone();
            T_Report report = GetEntity<T_Report>(T_Report._.Report_ID == reportid);

            StringBuilder Builder = new StringBuilder();

            string par = "";
            string qj = "";

            WhereClip wclip = T_ZBSJ._.ZBSJ_ReportID == reportid;
            wclip = wclip && T_ZBSJ._.ZBSJ_YEAR == year;
            switch (report.Report_Cycle)
            {
                case "01":
                    par = "月";
                    qj = string.Format("{0}年{1}{2}", year, month1, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "02":
                    par = "季度";
                    if (int.Parse(month1) > 12)
                    {
                        month1 = (int.Parse(month1) - 12).ToString();
                        month2 = (int.Parse(month2) - 12).ToString();
                    }
                    qj = string.Format("{0}年{1}{2}", year, month1, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "03":
                    qj = string.Format("{0}年", year);
                    break;
            }


            Builder.Append("<div style='padding:10px;text-align:center;'>");
            Builder.Append("<table id='TableMain' style='margin:auto;'>");


            DataTable ZbTable = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == reportid).ToDataTable();//查找指标
            ISourceList<T_Organization> orglist = DB.From<T_Organization>().Where(T_Organization._.Organization_ID.In(dwid)).ToList();


            if (dwid.Length > 0)
            {
                wclip = wclip && T_ZBSJ._.ZBSJ_DWID.In(dwid);


            }

            DataTable ZBSJTable = DB.From<T_ZBSJ>().Where(wclip).ToDataTable();

            Builder.AppendFormat("<tr><td class='tdcenter' >{0}</td>", qj);


            //拼纬度字符串
            for (int i = 0; i < wdcount; i++)
            {
                Builder.AppendFormat("<td class='tdcenter' colspan={1}>{0}</td>", wdarr[i], zbcount);
            }
            Builder.Append("</tr>");


            //拼指标字符串
            Builder.Append("<tr><td class='tdcenter'>单位名称</td>");
            for (int i = 0; i < wdcount; i++)
            {
                for (int j = 0; j < zbcount; j++)
                {
                    Builder.AppendFormat("<td class='tdcenter'>{0}</td>", zbarr[j]);
                }
            }
            Builder.Append("</tr>");





            foreach (T_Organization detail in orglist)
            {

                Builder.AppendFormat("<tr><td>{0}</td>", detail.Organization_Name);

                for (int i = 0; i < wdcount; i++)
                {

                    for (int j = 0; j < zbcount; j++)
                    {

                        DataRow[] rows = ZbTable.Select("zbzd_index='" + wdarr[i] + "' and zbzd_dimension='" + zbarr[j] + "'");
                        if (rows.Length > 0)
                        {
                            DataRow[] zbrows = ZBSJTable.Select("ZBSJ_ZBBH='" + rows[0]["ZBZD_ZBBH"] + "' and ZBSJ_DWID='" + detail.Organization_ID + "'");

                            if (zbrows.Length > 0)
                            {
                                double d = 0;
                                double value;
                                string m = string.Empty;
                                foreach (DataRow row in zbrows)
                                {

                                    if (double.TryParse(row["ZBSJ_VALUE"].ToString(), out value))//判断是否数字还是字符串，否则出错。
                                        //d += value;
                                        //如果是零，不显示，否则显示原值 ，可能有两位小数
                                        if (value == 0)
                                            m += "";
                                        else
                                            m += row["ZBSJ_VALUE"].ToString();

                                    else
                                    {
                                        m = m + row["ZBSJ_VALUE"].ToString() + "";
                                    }

                                }
                                Builder.AppendFormat("<td>{0}</td>", m);
                                //Builder.AppendFormat("<td>{0}{1}</td>", d, m);//为了显示数值的小数位，故不能用转化过的d，只能用row["ZBSJ_VALUE"].ToString()
                            }
                            else
                                Builder.AppendFormat("<td>{0}</td>", "");
                        }
                        else
                            Builder.AppendFormat("<td>{0}</td>", "");

                    }


                }
                Builder.Append("</tr>");
            }
            Builder.Append("</table>");
            Builder.Append("</div>");
            return Builder.ToString();

        }

        //此方法暂不用。
        public MemoryStream ExportExcel(string reportid, string year, string month1, string month2, string zb, string wd, string[] dwid, string title)
        {
            MemoryStream ms = new MemoryStream();


            zb = zb.Remove(zb.Length - 1);
            wd = wd.Remove(wd.Length - 1);

            string[] zbarr = zb.Split(',');
            string[] wdarr = wd.Split(',');
            int zbcount = zbarr.Length;
            int wdcount = wdarr.Length;
            // DataTable dtResult = ZbTable.Clone();
            T_Report report = GetEntity<T_Report>(T_Report._.Report_ID == reportid);

            StringBuilder Builder = new StringBuilder();


            NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            ISheet sheet = workbook.CreateSheet();

            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//对齐方式

            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直对齐方式


            ICellStyle cellStyle1 = workbook.CreateCellStyle();
            cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellStyle1.VerticalAlignment = VerticalAlignment.CENTER;
            string par = "";
            WhereClip wclip = T_ZBSJ._.ZBSJ_ReportID == reportid;



            string qj = "";
            switch (report.Report_Cycle)
            {
                case "01":
                    par = "月";
                    qj = string.Format("{0}年{1}{2}", year, month1, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "02":
                    par = "季度";
                    if (int.Parse(month1) > 12)
                    {
                        month1 = (int.Parse(month1) - 12).ToString();
                        month2 = (int.Parse(month2) - 12).ToString();
                    }
                    qj = string.Format("{0}年{1}{2}", year, month1, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "03":
                    qj = string.Format("{0}年", year);
                    break;
            }



            DataTable ZbTable = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == reportid).ToDataTable();//查找指标
            ISourceList<T_Organization> orglist = DB.From<T_Organization>().Where(T_Organization._.Organization_ID.In(dwid)).ToList();


            if (dwid.Length > 0)
            {
                wclip = wclip && T_ZBSJ._.ZBSJ_DWID.In(dwid);
                wclip = wclip && T_ZBSJ._.ZBSJ_YEAR == year;

            }

            DataTable ZBSJTable = DB.From<T_ZBSJ>().Where(wclip).ToDataTable();

            //插报表标题
            IRow titler = sheet.CreateRow(0);
            ICell icell = null;//其它单元格使用
            ICell icell1 = null;//大标题用
            icell1 = titler.CreateCell(0);
            icell1.CellStyle = cellStyle;
            icell1.SetCellValue(title);

            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 0, 0, zbcount * wdcount);
            sheet.AddMergedRegion(cellRangeAddress1);

            //插纬度标题
            IRow headerRow = sheet.CreateRow(1);
            icell = headerRow.CreateCell(0);
            icell.CellStyle = cellStyle1;
            icell.SetCellValue(qj);
            CellRangeAddress cellRangeAddresswd = new CellRangeAddress(1, 1, 0, 0);
            sheet.AddMergedRegion(cellRangeAddresswd);

            int dd = 0;
            int startcol = 1;
            for (int i = 0; i < wdcount; i++)
            {

                icell = headerRow.CreateCell(startcol);
                icell.CellStyle = cellStyle1;
                icell.SetCellValue(wdarr[i].ToString());
                dd = zbcount * (i + 1);
                cellRangeAddresswd = new CellRangeAddress(1, 1, startcol, dd);
                startcol = startcol + zbcount;
                sheet.AddMergedRegion(cellRangeAddresswd);

            }

            //插指标标题
            IRow Row1 = sheet.CreateRow(2);
            icell = Row1.CreateCell(0);
            icell.CellStyle = cellStyle1;
            icell.SetCellValue("单位名称");
            CellRangeAddress cellRangeAddresszb = new CellRangeAddress(2, 2, 0, 0);
            sheet.AddMergedRegion(cellRangeAddresszb);

            startcol = 1;

            for (int i = 0; i < wdcount; i++)
            {
                for (int j = 0; j < zbcount; j++)
                {


                    icell = Row1.CreateCell(startcol);
                    icell.CellStyle = cellStyle1;
                    icell.SetCellValue(zbarr[j].ToString());
                    startcol = startcol + 1;
                }
            }

            //插入实际指标数值。
            startcol = 0;
            int startrow = 3;
            foreach (T_Organization detail in orglist)
            {
                IRow Row2 = sheet.CreateRow(startrow);
                startrow = startrow + 1;
                //  Builder.AppendFormat("<tr><td>{0}</td>", detail.Organization_Name);
                Row2.CreateCell(startcol).SetCellValue(detail.Organization_Name);

                icell = Row2.CreateCell(startcol);
                icell.CellStyle = cellStyle;
                icell.SetCellValue(detail.Organization_Name);

                startcol = startcol + 1;
                for (int i = 0; i < wdcount; i++)
                {

                    for (int j = 0; j < zbcount; j++)
                    {

                        DataRow[] rows = ZbTable.Select("zbzd_index='" + wdarr[i] + "' and zbzd_dimension='" + zbarr[j] + "'");
                        if (rows.Length > 0)
                        {
                            DataRow[] zbrows = ZBSJTable.Select("ZBSJ_ZBBH='" + rows[0]["ZBZD_ZBBH"] + "' and ZBSJ_DWID='" + detail.Organization_ID + "'");

                            if (zbrows.Length > 0)
                            {
                                double d = 0;
                                double value;
                                string m = string.Empty;
                                foreach (DataRow row in zbrows)
                                {

                                    if (double.TryParse(row["ZBSJ_VALUE"].ToString(), out value))//判断是否数字还是字符串，否则出错。
                                        d += value;
                                    else
                                    {
                                        m = m + row["ZBSJ_VALUE"].ToString() + "";
                                    }
                                }




                                icell = Row2.CreateCell(startcol);
                                icell.CellStyle = cellStyle;
                                icell.SetCellValue(d);

                                startcol = startcol + 1;
                            }
                            else
                            {
                                //Builder.AppendFormat("<td>{0}</td>", "");


                                icell = Row2.CreateCell(startcol);
                                icell.CellStyle = cellStyle;
                                icell.SetCellValue("");
                                startcol = startcol + 1;
                            }
                        }
                        else
                        {
                            icell = Row2.CreateCell(startcol);
                            icell.CellStyle = cellStyle;
                            icell.SetCellValue("");
                            startcol = startcol + 1;
                        }
                        //Builder.AppendFormat("<td>{0}</td>", "");


                    }


                }
                startcol = 0;
            }

            int endrow = sheet.LastRowNum + 1;
            sheet.CreateRow(endrow);
            CellRangeAddress cellRangeAddressend = new CellRangeAddress(endrow, endrow, 0, zbcount * wdcount);
            sheet.AddMergedRegion(cellRangeAddressend);

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return ms;

        }

        //传一个sheet过来，将所有导出内容，写在同一个sheet内
        public ISheet ExportExcel(ISheet sheet, string reportid, string year, string month1, string month2, string zb, string wd, string[] dwid)
        {
            int CurrRow;


            zb = zb.Remove(zb.Length - 1);
            wd = wd.Remove(wd.Length - 1);

            string[] zbarr = zb.Split(',');
            string[] wdarr = wd.Split(',');
            int zbcount = zbarr.Length;
            int wdcount = wdarr.Length;
            // DataTable dtResult = ZbTable.Clone();
            T_Report report = GetEntity<T_Report>(T_Report._.Report_ID == reportid);

            StringBuilder Builder = new StringBuilder();


            NPOI.SS.UserModel.IWorkbook workbook = sheet.Workbook;

            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

            ICell icell = null;




            string par = "";
            WhereClip wclip = T_ZBSJ._.ZBSJ_ReportID == reportid;
            wclip = wclip && T_ZBSJ._.ZBSJ_YEAR == year;



            string qj = "";
            switch (report.Report_Cycle)
            {
                case "01":
                    par = "月";
                    qj = string.Format("{0}年{1}{2}", year, month1, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));//PadLeft用于将指定字符串，前用0补齐
                    break;
                case "02":
                    par = "季度";
                    if (int.Parse(month1) > 12)
                    {
                        month1 = (int.Parse(month1) - 12).ToString();
                        month2 = (int.Parse(month2) - 12).ToString();
                    }
                    qj = string.Format("{0}年{1}{2}", year, month1, par);
                    wclip = wclip && T_ZBSJ._.ZBSJ_MONTH.Between(month1.PadLeft(2, '0'), month2.PadLeft(2, '0'));
                    break;
                case "03":
                    qj = string.Format("{0}年", year);
                    break;
            }



            DataTable ZbTable = DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == reportid).ToDataTable();//查找指标
            ISourceList<T_Organization> orglist = DB.From<T_Organization>().Where(T_Organization._.Organization_ID.In(dwid)).ToList();


            if (dwid.Length > 0)
            {
                wclip = wclip && T_ZBSJ._.ZBSJ_DWID.In(dwid);
            }

            DataTable ZBSJTable = DB.From<T_ZBSJ>().Where(wclip).ToDataTable();



            //插纬度标题
            CurrRow = sheet.LastRowNum + 1;
            IRow headerRow = sheet.CreateRow(CurrRow);
            icell = headerRow.CreateCell(0);
            icell.CellStyle = cellStyle;
            icell.SetCellValue(qj);
            CellRangeAddress cellRangeAddresswd = null;

            int dd = 0;
            int startcol = 1;
            for (int i = 0; i < wdcount; i++)
            {

                icell = headerRow.CreateCell(startcol);
                icell.CellStyle = cellStyle;
                icell.SetCellValue(wdarr[i].ToString());
                dd = zbcount * (i + 1);
                cellRangeAddresswd = new CellRangeAddress(CurrRow, CurrRow, startcol, dd);
                startcol = startcol + zbcount;
                sheet.AddMergedRegion(cellRangeAddresswd);
                //设置合并后，单元格的边框
                ((NPOI.HSSF.UserModel.HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRangeAddresswd, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
            }

            //插指标标题
            CurrRow = sheet.LastRowNum + 1;
            IRow Row1 = sheet.CreateRow(CurrRow);
            icell = Row1.CreateCell(0);
            icell.CellStyle = cellStyle;
            icell.SetCellValue("单位名称");

            startcol = 1;

            for (int i = 0; i < wdcount; i++)
            {
                for (int j = 0; j < zbcount; j++)
                {
                    icell = Row1.CreateCell(startcol);
                    icell.CellStyle = cellStyle;
                    icell.SetCellValue(zbarr[j].ToString());
                    startcol = startcol + 1;
                }
            }

            //插入实际指标数值。
            startcol = 0;

            //int startrow = CurrRow;
            foreach (T_Organization detail in orglist)
            {
                CurrRow = sheet.LastRowNum + 1;
                IRow Row2 = sheet.CreateRow(CurrRow);
                //startrow = startrow + 1;
                Row2.CreateCell(startcol).SetCellValue(detail.Organization_Name);

                icell = Row2.CreateCell(startcol);
                icell.CellStyle = cellStyle;
                icell.SetCellValue(detail.Organization_Name);

                startcol = startcol + 1;
                for (int i = 0; i < wdcount; i++)
                {

                    for (int j = 0; j < zbcount; j++)
                    {

                        DataRow[] rows = ZbTable.Select("zbzd_index='" + wdarr[i] + "' and zbzd_dimension='" + zbarr[j] + "'");
                        if (rows.Length > 0)
                        {
                            DataRow[] zbrows = ZBSJTable.Select("ZBSJ_ZBBH='" + rows[0]["ZBZD_ZBBH"] + "' and ZBSJ_DWID='" + detail.Organization_ID + "'");

                            if (zbrows.Length > 0)
                            {
                                double d = 0;
                                double value;
                                string m = string.Empty;
                                foreach (DataRow row in zbrows)
                                {

                                    if (double.TryParse(row["ZBSJ_VALUE"].ToString(), out value))//判断是否数字还是字符串，否则出错。
                                        //d += value;
                                        //如果是零，不显示，否则显示原值 ，可能有两位小数
                                        if (value == 0)
                                            m += "";
                                        else
                                            m += row["ZBSJ_VALUE"].ToString() + "";
                                    else
                                    {
                                        m += row["ZBSJ_VALUE"].ToString() + "";
                                    }
                                }

                                icell = Row2.CreateCell(startcol);
                                icell.CellStyle = cellStyle;
                                icell.SetCellValue(m);

                                startcol = startcol + 1;
                            }
                            else
                            {
                                //Builder.AppendFormat("<td>{0}</td>", "");


                                icell = Row2.CreateCell(startcol);
                                icell.CellStyle = cellStyle;
                                icell.SetCellValue("");
                                startcol = startcol + 1;
                            }
                        }
                        else
                        {
                            icell = Row2.CreateCell(startcol);
                            icell.CellStyle = cellStyle;
                            icell.SetCellValue("");
                            startcol = startcol + 1;
                        }
                        //Builder.AppendFormat("<td>{0}</td>", "");


                    }


                }
                startcol = 0;
            }

            //插两行空行
            CurrRow = sheet.LastRowNum + 1;
            sheet.CreateRow(CurrRow);
            CurrRow = sheet.LastRowNum + 1;
            sheet.CreateRow(CurrRow);
            //CellRangeAddress cellRangeAddressend = new CellRangeAddress(endrow, endrow, 0, zbcount * wdcount);
            //sheet.AddMergedRegion(cellRangeAddressend);

            //workbook.Write(ms);
            //ms.Flush();
            //ms.Position = 0;

            return sheet;

        }

    }
}
